#!/bin/bash
# 定义变量：数据同步日期，表示同步哪一条日志数据
# 第1、执行shell脚本时，传递参数
# 第2、如果没有传递参数，同步前一天数据
if [ -n "$1" ] ; then
data_date=$1
else
data_date=`date -d "-1 days" +%F`
fi

ADS_TRAFFIC_PAGE_VIEW_REPORT_SQ=+"
INSERT OVERWRITE TABLE jtp_app_ads.ads_traffic_page_view_report
SELECT dt,
recent_days,
province,
city,
page_view_count,
user_count,
session_count,
avg_user_session_count,
session_during_time,
avg_session_during_time,
bounce_count,
bounce_count_rate
FROM jtp_app_ads.ads_traffic_page_view_report
UNION
SELECT
'${data_date}' AS dt
, 1 AS recent_days
, province
, city
, sum(session_pv) AS page_view_count
, count(DISTINCT device_id) AS user_count
, count(session_id) AS session_count
, round(count(session_id) /count(DISTINCT device_id) , 2) AS avg_user_session_count
, round(sum(session_during_time) / 1000 / 60, 2) AS session_during_time
, round(avg(session_during_time) / 1000 / 60, 2) AS avg_session_during_time
, sum(if(session_pv = 1, 1, 0)) AS bounce_count
, round(sum(if(session_pv = 1, 1, 0)) / count(session_pv) , 4) AS bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt = '${data_date}' AND province IS NOT NULL  AND city IS NOT NULL
GROUP BY province, city ;
"
# 执行SQL语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL}"